package com.laizhenghua.example.service.impl;

import com.laizhenghua.example.configuration.ColumnConfig;
import com.laizhenghua.example.configuration.ExcelConfig;
import com.laizhenghua.example.configuration.QueryParamDTO;
import com.laizhenghua.example.dao.UserDao;
import com.laizhenghua.example.service.ExcelService;
import com.laizhenghua.example.utils.XmlParser;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @description:
 * @date: 2022/4/26 13:30
 */
@Service(value = "excelService")
public class ExcelServiceImpl implements ExcelService {
    private static final org.apache.logging.log4j.Logger log = org.apache.logging.log4j.LogManager.getLogger(ExcelServiceImpl.class);

    @Autowired
    private UserDao userDao;

    @Override
    public ExcelConfig getConfigInfo(QueryParamDTO queryParam) {
        ExcelConfig configInfo = XmlParser.getConfigInfo(queryParam.getFilePath(), queryParam.getFileName(), queryParam.getConfigName());
        return configInfo;
    }

    @Override
    public Map<String, String> importData(MultipartFile file, QueryParamDTO queryParam) {
        Map<String, String> result = new HashMap<>();
        // 1.获取Excel标题、实体、表字段的映射关系
        ExcelConfig configInfo = XmlParser.getConfigInfo(queryParam.getFilePath(), queryParam.getFileName(), queryParam.getConfigName());
        if (configInfo == null) {
            result.put("error", "导入失败无法找到配置信息！");
            return result;
        }
        // 2.解析Excel数据
        List<Map<String, Cell>> excelData = resolveExcelData(file);
        if (excelData == null || excelData.size() == 0) {
            result.put("error", "导入失败Excel文件解析失败");
            return result;
        }
        // 3.基于配置文件封装Excel数据(封装成实体)
        List<String> entityList = convertEntity(configInfo, excelData, String.class);
        if (CollectionUtils.isEmpty(entityList)) {
            result.put("error", "获取实体信息失败");
            return result;
        }

        return null;
    }

    @Override
    public List<Map<String, Cell>> resolveExcelData(MultipartFile file) {
        List<Map<String, Cell>> dataList = new ArrayList<>();
        InputStream in = null;
        try {
            in = file.getInputStream();
            String name = file.getOriginalFilename();
            Workbook wb = null;
            if (name.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(in);
            } else if (name.endsWith(".xls")) {
                wb = new HSSFWorkbook(in);
            } else {
                return null;
            }
            // 默认为0
            Sheet sheet = wb.getSheetAt(0);
            // 标题集合
            List<String> titleList = getTitle(sheet);
            int rowNum = sheet.getLastRowNum() + 1;
            int cellNum = titleList.size();
            for (int i = 1; i < rowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                Map<String, Cell> excelMap = new HashMap<>();
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        excelMap.put(titleList.get(j), null);
                        continue;
                    }
                    excelMap.put(titleList.get(j), cell);
                }
                dataList.add(excelMap);
            }
        } catch (IOException e) {
            log.error(e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    log.error(e);
                }
            }
        }
        return dataList;
    }
    /**
     * 解析Excel标题
     * @param sheet 工作簿
     * @return 标题集合
     */
    private List<String> getTitle(Sheet sheet) {
        List<String> list = new ArrayList<>();
        Row row = sheet.getRow(0);
        for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
            Cell cell = row.getCell(i);
            list.add(cell.getStringCellValue());
        }
        return list;
    }
    /**
     * 根据映射信息，将Excel文件数据赋值给实体类
     * @param config 配置信息
     * @param data Excel数据
     * @return
     */
    @Override
    public <E> List<E> convertEntity(ExcelConfig config, List<Map<String, Cell>> data, Class<E> clazz) {
        List<E> entityList = new ArrayList<>();
        List<ColumnConfig> columnConfigList = config.getColumnConfigList();
        E object = null;
        flag:
        for (Map<String, Cell> map : data) {
            try {
                assert clazz != null;
                object = (E) clazz.newInstance();
            } catch (Exception e) {
                log.error(e);
                return null;
            }
            for (ColumnConfig columnConfig : columnConfigList) {
                String title = columnConfig.getTitle(); // excel标题
                String name = columnConfig.getName(); // 实体属性名
                String type = columnConfig.getType(); // 实体属性类型
                Boolean notNull = columnConfig.getNotNull();
                Cell cell = map.get(title);
                Object cellValue = parseCellValue(cell);
                if (notNull) {
                    // title对应的值不允许为空
                    if (ObjectUtils.isEmpty(cellValue)) {
                        // result.put(cell.getAddress().toString(), "导入失败，失败原因不允许为空！");
                        continue flag;
                    }
                }
                // 获取实体类全部属性并赋值
                Field[] fields = object.getClass().getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    if (field.getName().equals(name) && field.getType().getSimpleName().equals(type)) {
                        try {
                            Object value = null;
                            value = getValue(cellValue.toString(), field.getType());
                            field.set(object, value);
                        } catch (Exception e) {
                            log.error(e);
                        }
                    }
                }
            }
            entityList.add(object);
        }
        return entityList;
    }

    @Override
    public <E> void writeOutputStreamByConfig(ExcelConfig config, List<E> list, FileOutputStream fos) {
        // 创建Excel文件
        Workbook wb = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = wb.createSheet(config.getName());
        // 设置单元格居中
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.GENERAL);
        
        // 单元格赋值
        Row titleRow = null; // 标题列
        int index = 0; // 列索引
        List<ColumnConfig> columnConfigList= config.getColumnConfigList();
        for (E entity : list) {
            Field[] fields = entity.getClass().getDeclaredFields();
            Row row = sheet.createRow(index + 1);
            int cellIndex = 0; // 单元格索引
            for (ColumnConfig column : columnConfigList) {
                String title = column.getTitle(); // excel标题
                String name = column.getName(); // 实体属性名
                String type = column.getType(); // 属性类型
                for (Field field : fields) {
                    if (field.getName().equals(name) && field.getType().getSimpleName().equals(type)) {
                        if (index == 0) {
                            if (titleRow == null) {
                                titleRow = sheet.createRow(index);
                            }
                            Cell cell = titleRow.createCell(cellIndex);
                            cell.setCellValue(title);
                        }
                        Cell cell = row.createCell(cellIndex);
                        try {
                            field.setAccessible(true); // 取消访问检查
                            Object value = field.get(entity);
                            setCellValue(cell, value, field.getType());
                        } catch (Exception e) {
                            log.error(e);
                        }
                        cellIndex++;
                    }
                }
            }
            index++;
        }
        try {
            wb.write(fos);
        } catch (IOException e) {
            log.error(e);
        }
    }

    @Override
    public void write(InputStream inputStream, OutputStream outputStream) {
        byte[] buffer = new byte[4096];
        try {
            int count = inputStream.read(buffer, 0, buffer.length);
            while (count != -1) {
                outputStream.write(buffer, 0, count);
                count = inputStream.read(buffer, 0, buffer.length);
            }
        } catch (RuntimeException e) {
            throw e;
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    /**
     * Excel数据类型转换
     * @param cell 单元格
     * @return
     */
    private Object parseCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        Object cellValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if (date != null) {
                        cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);
                    } else {
                        cellValue = "";
                    }
                } else {
                    cellValue = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            case FORMULA:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = cell.getDateCellValue();
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case STRING:
                cellValue = cell.getRichStringCellValue().getString();
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }
    /**
     * 类型转换，把val转换成type类型返回 比如说getValue("123", Integer.class) 返回一个123
     * @param val
     * @param type
     * @param <T>
     * @return
     */
    public <T> T getValue(String val, Class<T> type) {
        T value = null;
        try {
            if ("Date".equals(type.getSimpleName())) {
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                value = (T) simpleDateFormat.parse(val);
                return value;
            }
            Constructor<T> constructor = type.getConstructor(String.class);
            constructor.setAccessible(true);
            value = constructor.newInstance(val);
        } catch (Exception e) {
            log.error(e);
        }
        return value;
    }
    public <T> Cell setCellValue(Cell cell, Object value, Class<T> type) {
        switch (type.getSimpleName()) {
            case "Date":
                cell.setCellValue((Date) value);
            case "Integer":
                assert value instanceof Integer;
                cell.setCellValue((Integer) value);
            case "String":
                cell.setCellValue(value.toString());
            default:
                cell.setCellValue(value.toString());
        }
        return cell;
    }
}
